/**
	AUTHOR		: SUJAN
	DATE		: FEB 10th 2004
	DESCRIPTION	: THIS PROCEDURE IS USED TO INSERT OR UPDATE THE COLUMN DISPLAY DETAILS 
			  IN SALES FORECAST AND BUILD FORECAST SCREENS.
*/

ALTER PROC SP_SYNCHRONIZE_EDITCOLDISP AS
BEGIN
	DECLARE @FCENTRYSETUP_CURSOR AS CURSOR
	DECLARE @USERID_CURSOR AS CURSOR
	DECLARE @ENTRYNAME VARCHAR ( 100 )
	DECLARE @ROWCNT INT
	DECLARE @USERID VARCHAR(20)
	DECLARE @MAXSORTORDER INT

	DELETE FROM EDITCOLDISP WHERE TABLENAME = 'SALESFORECAST' AND COLNAME NOT IN ( SELECT ENTRYNAME FROM FCENTRYSETUP WHERE ENTRYSELECT = 0 )	

	SET @ROWCNT = 0
	SET @USERID_CURSOR = CURSOR FOR SELECT DISTINCT USERID FROM EDITCOLDISP WHERE TABLENAME = 'SALESFORECAST'
	SET @FCENTRYSETUP_CURSOR = CURSOR FOR SELECT ENTRYNAME FROM FCENTRYSETUP WHERE ENTRYSELECT = 0

	OPEN @FCENTRYSETUP_CURSOR
	FETCH NEXT FROM @FCENTRYSETUP_CURSOR INTO @ENTRYNAME
	WHILE @@FETCH_STATUS = 0
		BEGIN
		SELECT @ROWCNT = COUNT(*) FROM EDITCOLDISP WHERE COLNAME = @ENTRYNAME AND TABLENAME = 'SALESFORECAST' AND USERID IS NULL
		--CHECKING IF THERE IS AN ENTRY IN EDITCOLDISP IF NOT INSERT IT TO THE EVERY USER..
		--HERE WE R MAINTAINIG THE ROWS WITH NULL USERID BECAUSE THESE R TREATED AS THE BASIC COLUMNS
		IF ( @ROWCNT = 0 )
		BEGIN
			SET @MAXSORTORDER = NULL
			SELECT @MAXSORTORDER = MAX(SORTORDER) FROM EDITCOLDISP WHERE TABLENAME = 'SALESFORECAST' AND USERID IS NULL
			IF ( @MAXSORTORDER IS NULL )
			BEGIN
				SET @MAXSORTORDER = 1
			END
			ELSE
			BEGIN
				SET @MAXSORTORDER = @MAXSORTORDER + 1
			END
			INSERT INTO EDITCOLDISP ( TABLENAME, COLNAME, COLDISPLAY, SORTORDER, USERID ) VALUES ( 'SALESFORECAST', @ENTRYNAME, @ENTRYNAME, @MAXSORTORDER, NULL )
			OPEN @USERID_CURSOR
			FETCH NEXT FROM @USERID_CURSOR INTO @USERID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF ( @USERID IS NOT NULL )
				BEGIN
					SET @MAXSORTORDER = NULL
					SELECT @MAXSORTORDER = MAX(SORTORDER) FROM EDITCOLDISP WHERE TABLENAME = 'SALESFORECAST' AND USERID = @USERID
					IF ( @MAXSORTORDER IS NULL )
					BEGIN
						SET @MAXSORTORDER = 1
					END
					ELSE
					BEGIN
						SET @MAXSORTORDER = @MAXSORTORDER + 1
					END
					INSERT INTO EDITCOLDISP ( TABLENAME, COLNAME, COLDISPLAY, SORTORDER, USERID ) VALUES ( 'SALESFORECAST', @ENTRYNAME, @ENTRYNAME, @MAXSORTORDER, @USERID )
				END
				SET @USERID = NULL
				FETCH NEXT FROM @USERID_CURSOR INTO @USERID
			 END
			CLOSE @USERID_CURSOR
		 END
		 FETCH NEXT FROM @FCENTRYSETUP_CURSOR INTO @ENTRYNAME
	END
	CLOSE @FCENTRYSETUP_CURSOR	
	----
	
	--DELETING THE COLUMN NAMES WHICH ARE NOT SELECTED IN FORECAST ENTRY SETUP	
	DELETE FROM EDITCOLDISP WHERE TABLENAME = 'BUILDFORECAST' AND COLNAME NOT IN ( SELECT ENTRYNAME FROM FCENTRYSETUP WHERE ENTRYSELECT = 0 )
	
	SET @ROWCNT = 0
	SET @USERID_CURSOR = CURSOR FOR SELECT DISTINCT USERID FROM EDITCOLDISP WHERE TABLENAME = 'BUILDFORECAST'
	SET @FCENTRYSETUP_CURSOR = CURSOR FOR SELECT ENTRYNAME FROM FCENTRYSETUP WHERE ENTRYSELECT = 0

	OPEN @FCENTRYSETUP_CURSOR
	FETCH NEXT FROM @FCENTRYSETUP_CURSOR INTO @ENTRYNAME
	WHILE @@FETCH_STATUS = 0
		BEGIN
		SELECT @ROWCNT = COUNT(*) FROM EDITCOLDISP WHERE COLNAME = @ENTRYNAME AND TABLENAME = 'BUILDFORECAST' AND USERID IS NULL
		--CHECKING IF THERE IS AN ENTRY IN EDITCOLDISP IF NOT INSERT IT TO THE EVERY USER..
		--HERE WE R MAINTAINIG THE ROWS WITH NULL USERID BECAUSE THESE R TREATED AS THE BASIC COLUMNS
		IF ( @ROWCNT = 0 )
		BEGIN
			SET @MAXSORTORDER = NULL
			SELECT @MAXSORTORDER = MAX(SORTORDER) FROM EDITCOLDISP WHERE TABLENAME = 'BUILDFORECAST' AND USERID IS NULL
			IF ( @MAXSORTORDER IS NULL )
			BEGIN
				SET @MAXSORTORDER = 1
			END
			ELSE
			BEGIN
				SET @MAXSORTORDER = @MAXSORTORDER + 1
			END
			INSERT INTO EDITCOLDISP ( TABLENAME, COLNAME, COLDISPLAY, SORTORDER, USERID ) VALUES ( 'BUILDFORECAST', @ENTRYNAME, @ENTRYNAME, @MAXSORTORDER, NULL )
			OPEN @USERID_CURSOR
			FETCH NEXT FROM @USERID_CURSOR INTO @USERID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF ( @USERID IS NOT NULL )
				BEGIN
					SET @MAXSORTORDER = NULL
					SELECT @MAXSORTORDER = MAX(SORTORDER) FROM EDITCOLDISP WHERE TABLENAME = 'BUILDFORECAST' AND USERID = @USERID
					IF ( @MAXSORTORDER IS NULL )
					BEGIN
						SET @MAXSORTORDER = 1
					END
					ELSE
					BEGIN
						SET @MAXSORTORDER = @MAXSORTORDER + 1
					END
					INSERT INTO EDITCOLDISP ( TABLENAME, COLNAME, COLDISPLAY, SORTORDER, USERID ) VALUES ( 'BUILDFORECAST', @ENTRYNAME, @ENTRYNAME, @MAXSORTORDER, @USERID )
				END
				SET @USERID = NULL
				FETCH NEXT FROM @USERID_CURSOR INTO @USERID
			END
			CLOSE @USERID_CURSOR
		 END
		 FETCH NEXT FROM @FCENTRYSETUP_CURSOR INTO @ENTRYNAME
	END
	CLOSE @FCENTRYSETUP_CURSOR
		
	DEALLOCATE @USERID_CURSOR
	DEALLOCATE @FCENTRYSETUP_CURSOR
	
END

